Preparing a SQL Server Database

SilhouetteCentral stores all patient demographic and assessment data in a SQL server database. Depending upon your expected scale and existing IT infrastructure this database can be hosted on the same server running the SilhouetteCentral website, or it can be hosted on a dedicated database server.

Using an existing SQL Server Installation

If an existing instance of SQL Server has been designated to host the SilhouetteCentral database, you need to obtain the required connection details to access the database. These details include:

• SQL Server Instance name, i.e. serverName\instanceName.
• Authentication method (Windows Authentication or SQL Server). If the authentication method selected is "SQL Server," you need the associated username and password.
• Database name, i.e. Silhouette.

Your Database Administrator (DBA) should be able to create the database and provide these details to you. The database can be created empty and SilhouetteCentral populates it during the installation procedure.

Installing SQL Server

If SilhouetteConnect is being supported, then SilhouetteCentral will need access to a SQL Server 2014 instance to be used for the Synchronization process. There are two common cases for the synchronization instance:

1. The main database is hosted on an external server, and only the synchronization instance will be hosted on the server running the SilhouetteCentral website. In this case SQL Server 2014 Express should be installed as the named instance "Silhouette" as documented below.
2. Both the main database and the synchronization instance will be hosted on the server running the SilhouetteCentral website.
   1. If the main database server instance is SQL Server 2014, then this instance will able to be used for both the main and synchronization instance.
   2. If the main database server instance is not SQL Server 2014, then both the main server instance and an instance of SQL Server 2014 Express should be installed as documented below. By default the synchronization instance should be the named instance "Silhouette".

The exact instructions to install SQL Server depend on the SQL server edition and version. The basic steps are provided below, derived from SQL Server 2014, but it is recommended to read and follow the instructions that come with SQL Server.

1. Run the SQL Server installation executable and click the OK button on the “Choose Directory For Extracted Files” dialog.
2. On the “SQL Server Installation Center” dialog, select the “New SQL Server stand-alone installation or add features to an existing installation” link (towards top right corner of dialog).
3. The SQL Server 2014 Setup wizard starts.
4. Select “I accept the license terms” and click Next.
5. On the Feature Selection step ensure the following features are enabled and click Next.
   1. Database Engine Services
   2. Management Tools – Basic
6. On the Instance Configuration step, select the “Named Instance” option and give the SQL server instance an appropriate name, e.g. SQLSILHOUETTE, then click Next.
7. On the Server Configuration, step click Next.
8. On the Database Engine Configuration step, select the “Windows authentication mode” option and click Next.
9. Follow any additional prompts that appear until installation is completed.

Creating a SilhouetteCentral Database

Once SQL Server has been installed a blank database must be created to store all clinical assessment data. This can be created in a number of ways including:

• SQL Management Studio
• Windows Powershell

Creating a Database using SQL Management Studio

• From the Windows start menu start SQL Management Studio.
• A Connect to Server dialog should appear. If not, select Connect Object Explorer within the File menu.
• Set the Server type drop down to Database Engine and in the Server name box type .\<SQL SERVER NAME> (e.g. .\SQLSILHOUETTE) then click Connect.
• Within the object explorer pane (left side of screen), right click on Databases and select New Database….
• Within the New Database dialog type in the database name Silhouette and press OK.
• Within the object explorer pane right click on Security and select Login… underneath the New submenu.
• Type IIS AppPool\Silhouette into the Login name text box.
• Select Silhouette within the Default database drop down.
• Click OK.
• Within the object explorer pane expand the Databases item and further expand the sub-item representing the SilhouetteCentral database.
• Right click on Security and select User… underneath the New submenu.
• Type IIS AppPool\Silhouette into both the User name and Login name text boxes.
• In the Membership section scroll down and place a tick beside db_owner.
• Click OK.

Creating a Database using Windows Powershell

Creation of the SilhouetteCentral database may also be scripted via a powershell command prompt:

Invoke-Sqlcmd –ServerInstance .\SILHOUETTE –Query "CREATE DATABASE Silhouette"

Invoke-Sqlcmd –ServerInstance .\SILHOUETTE –Query "CREATE LOGIN

[IIS AppPool\Silhouette] FROM WINDOWS WITH DEFAULT_DATABASE=Silhouette"

Invoke-Sqlcmd –ServerInstance .\SILHOUETTE –Query "USE Silhouette CREATE USER [IIS AppPool\Silhouette] FOR LOGIN [IIS AppPool\Silhouette];"

Required permissions on the main database:

Invoke-Sqlcmd –ServerInstance .\SILHOUETTE –Query "USE Silhouette exec sp_addrolemember 'db_owner', [IIS AppPool\Silhouette]"

Required permissions on the synchronization instance:

Invoke-Sqlcmd –ServerInstance .\SILHOUETTE –Query "exec sp_addsrvrolemember [IIS AppPool\Silhouette] , 'sysadmin'"

Synchronization Database Configuration

By default SilhouetteCentral expects the synchronization instance to be installed as the named instance "Silhouette" on the same machine. However if this is not the case, then the connection string used by SilhouetteCentral will need to be customized by editing the MachineSettings configuration file, see SilhouetteCentral Web Application Configuration.